--POUR CONTROLE


define erp='erp_mli2.';
define outils='outils_mli2.';

-- CALL uvse_ExistsOrCreate('ART_COMPDEPOT');

 DELETE FROM se_ART_COMPDEPOT;
/*


--SELECT majsto,emplacement,MAGRGT,MAGPCK,CLASVOLUME,TYPERGT,p.* FROM uv_pproduits p WHERE id LIKE '54870771449%'
--SELECT * FROM &erp.art_compdepot WHERE comp_num =134299

update erp_mli2.art_compdepot dd set prfempl_cod=decode(dd.depot_cod,'01','P','AUCUN') where exists (
 SELECT c.gene_cod,c.combsto_id,d.depot_cod, d.prfempl_cod,c.complet_typ
 FROM erp_mli2.art_compdepot d,erp_mli2.art_comp c
 WHERE c.comp_num=d.comp_num
   and  d.prfempl_cod not in ('E','P','S','C','B','R','SANS','AUCUN')
   and c.complet_typ='S'
   and d.enreg_id=dd.enreg_id)
 group by   d.prfempl_cod,c.complet_typ
 order by complet_typ
                     select distinct p.id,p.TYPERGT from  uv_pproduits p where p.id like 'YVO0%'

/*d�pots de progres*/
 INSERT INTO se_ART_COMPDEPOT
 (SELECT DISTINCT
         Nvl((SELECT comp_num FROM se_art_gene g WHERE g.gene_cod || g.gene_ind =p.prodbase),0) comp_num,
         dep.depot depot_cod,
         dep.RANGMULTI2+1 compdepot_rang,
         Nvl(Decode(p.majsto,'AM','AA',p.majsto),'AA') prfsto_cod,
         'STMINI_ECO' prfrupt_cod,
         dep.depot depotempl_cod,
         Nvl(p.TYPERGT,'AUCUN') prfempl_cod,
         'AUCUN' prflot_cod,
         'AUCUN' prfserie_cod,
         'AUCUN' prfidentstk_cod,
         'MANUEL' prfinvent_cod,
         0 enreg_id,
         'N' enreg_s,
         NULL rgtcase_id,
         NULL pckcase_id,
         NULL emplfam_cod,
         NULL fournisseur_cod,
         NULL abc_cod,
         NULL emplabc_cod,
         NULL invtabc_cod,
         NULL invtdern_dat,
         NULL invtproch_dat,
         NULL adrstock_cod,
         NULL adrstock2_cod,
         NULL adrstock3_cod,
         NULL adrstock4_cod,
         NULL adrstock5_cod,
         Decode(p.frequence,0,NULL) frequencenbs_val,
         NULL fraisappro_mt,
         NULL delaiplus_nb
  FROM uv_pproduits_depot dep, uv_pproduits p,uv_pproduits_statut_actuel s
  WHERE p.id=dep.id
    AND s.id=p.id AND (s.statut<900 OR s.statut IS NULL)
    AND p.codgestion in ('2','1') AND p.prodbase=p.ID
 ) ;

/*d�p�t principal des SFA de l'article, manquant dans l'article*/
 INSERT INTO se_ART_COMPDEPOT
 (SELECT DISTINCT
         unf.comp_num comp_num,
         dep.depot_cod depot_cod,
         999 compdepot_rang,
         'AA' prfsto_cod,
         'STMINI_ECO' prfrupt_cod,
         dep.depot_cod depotempl_cod,
         'AUCUN' prfempl_cod,
         'AUCUN' prflot_cod,
         'AUCUN' prfserie_cod,
         'AUCUN' prfidentstk_cod,
         'MANUEL' prfinvent_cod,
         0 enreg_id,
         'N' enreg_s,
         NULL rgtcase_id,
         NULL pckcase_id,
         NULL emplfam_cod,
         NULL fournisseur_cod,
         NULL abc_cod,
         NULL emplabc_cod,
         NULL invtabc_cod,
         NULL invtdern_dat,
         NULL invtproch_dat,
         NULL adrstock_cod,
         NULL adrstock2_cod,
         NULL adrstock3_cod,
         NULL adrstock4_cod,
         NULL adrstock5_cod,
         null frequencenbs_val,
         NULL fraisappro_mt,
         NULL delaiplus_nb
  FROM se_ART_COMPUNFAB unf,&erp.par_unfabdep dep
  WHERE unf.unfab_cod=dep.unfab_cod
    AND (SELECT Min(unfabdep_rang) FROM &erp.par_unfabdep dep2 where dep2.unfab_cod=dep.unfab_cod)=dep.unfabdep_rang
    AND NOT EXISTS (SELECT 1 FROM se_ART_COMPDEPOT dep3 WHERE dep3.comp_num=unf.comp_num AND dep3.depot_cod=dep.depot_cod)
 ) ;

 UPDATE se_ART_COMPDEPOT tf SET enreg_id=(select enreg_id FROM &erp.ART_COMPDEPOT f WHERE f.comp_num=tf.comp_num AND f.depot_cod=tf.depot_cod ) ;
 UPDATE se_ART_COMPDEPOT tf SET enreg_id=&outils.enreg_id.NEXTVAL WHERE enreg_id IS NULL OR enreg_id =0;

/*ordrage des rangs des d�p�ts dans l'article*/
  UPDATE se_ART_COMPDEPOT c SET compdepot_rang =
  (SELECT Min(rang) FROM
      (SELECT COMP_NUM,depot_cod,compdepot_rang,
        Rank() OVER (PARTITION BY COMP_NUM order BY COMP_NUM,compdepot_rang,depot_cod) rang
      FROM se_ART_COMPDEPOT) r
      WHERE r.comp_num=c.comp_num AND r.depot_cod=c.depot_cod
  ) ;

UPDATE se_ART_COMPDEPOT SET prfempl_cod='AUCUN' WHERE prfempl_cod='SANS' ;


UPDATE se_ART_COMPDEPOT SET PRFEMPL_COD ='AVEC' WHERE depot_cod IN ('01','05','06','07') AND PRFEMPL_COD ='AUCUN' ;

SELECT DISTINCT 'ART_PRFEMPL manquant.txt' outname,prfempl_cod, depotempl_cod FROM se_ART_COMPDEPOT g
  WHERE NOT EXISTS (sELECT depot_cod,prfempl_cod FROM erp_mli2.art_prfempl p
                    WHERE p.depot_cod=g.depotempl_cod AND p.prfempl_cod=g.prfempl_cod) ;


 CALL uvse_synchroverserp('comp_num@depot_cod','ART_COMPDEPOT','O') ;



commit;






select * from (
SELECT &outils.stragg(source) source,comp_num, depot_cod, compdepot_rang, prfsto_cod, prfrupt_cod, depotempl_cod, prfempl_cod, prflot_cod, prfserie_cod, prfidentstk_cod, prfinvent_cod, enreg_id, enreg_s, rgtcase_id, pckcase_id, emplfam_cod, fournisseur_cod, abc_cod, emplabc_cod, invtabc_cod, invtdern_dat, invtproch_dat, adrstock_cod, adrstock2_cod, adrstock3_cod, adrstock4_cod, adrstock5_cod, frequencenbs_val, fraisappro_mt, delaiplus_nb
FROM (
  SELECT 'uv' source ,g.* FROM se_ART_COMPDEPOT g
  UNION ALL
  SELECT 'se' source ,g.* FROM &erp.ART_COMPDEPOT g, &erp.art_refe r where r.comp_num=g.comp_num
  )

GROUP BY  comp_num, depot_cod, compdepot_rang, prfsto_cod, prfrupt_cod, depotempl_cod, prfempl_cod, prflot_cod, prfserie_cod, prfidentstk_cod, prfinvent_cod, enreg_id, enreg_s, rgtcase_id, pckcase_id, emplfam_cod, fournisseur_cod, abc_cod, emplabc_cod, invtabc_cod, invtdern_dat, invtproch_dat, adrstock_cod, adrstock2_cod, adrstock3_cod, adrstock4_cod, adrstock5_cod, frequencenbs_val, fraisappro_mt, delaiplus_nb
HAVING Count(1)!=2 ) r
ORDER BY comp_num,depot_cod;
